/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.uca.sisbec.helpers.reportes;

import com.mysql.jdbc.PreparedStatement;
import com.mysql.jdbc.Statement;
import com.uca.sisbec.beans.ReporteCumXCarreraBean;
import com.uca.sisbec.commons.Utils;
import com.uca.sisbec.forms.reportes.ReporteCumPorCarreraForm;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import org.hibernate.Session;

/**
 *
 * @author Susy
 */
public class ReporteCumPorCarreraHelper{
    private Session session;
    
    public ReporteCumPorCarreraHelper(Session session){
        this.session=session;
    }
    
    public List<ReporteCumXCarreraBean> getReportList(ReporteCumPorCarreraForm form) throws SQLException{
        Statement ps= (Statement) session.connection().createStatement();
        List<ReporteCumXCarreraBean> list = new ArrayList<ReporteCumXCarreraBean>();
        ResultSet rs;
        String sql = getSql();
        int param=1;
        if(!Utils.isNullOrEmpty(form.getEstado()))
            sql += " and b.estado = '"+form.getEstado()+"' ";
        if(form.getCarrera()!=0)
            sql += " and b.cod_carrera = "+form.getCarrera()+" ";
        if(form.getUniversidad()!=0)
            sql += " and b.id_univ = "+form.getUniversidad()+" ";
        sql += " group by b.carnet, b.nombres, b.apellidos, b.estado, c.carrera, u.nombre ";
        rs=ps.executeQuery(sql);
        while(rs.next()){
            list.add(new ReporteCumXCarreraBean(rs));
        }
        return list;
    }
    
    private String getSql(){
       String sql="";
       sql="select "
               + "b.carnet,"
               + "concat(b.nombres, ' ', b.apellidos) nombre,"
               + "    sum(mb.nota_final * m.uv) / sum(m.uv) cum,"
               + "    c.carrera,"
               + "u.nombre universidad,"
               + "(select count(1) from materia_x_becario mb2, becario b2 where mb2.carnet = b2.carnet and b2.cod_carrera = b.cod_carrera) materias,"
               + "b.estado "
               + "from "
               + "becario b, "
               + "carrera c,"
               + "universidad u,"
               + "materia m, "
               + "carrera_X_becario cb,"
               + "materia_X_Becario mb,"
               + "materia_x_carrera mc where "
               + "mb.carnet = b.carnet "
               + "and cb.carnet = b.carnet "
               + "and c.cod_carrera = cb.cod_carrera "
               + "and mb.cod_materia = m.cod_materia "
               + "and mc.cod_carrera = c.cod_carrera "
               + "and m.cod_materia = mc.cod_materia "
               + "and c.id_univ = u.id_univ ";
               
       return sql;
    }
    
}
